﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.ServiceModel;
using VAdvantage.Utility;
using VAdvantage.ImpExp;
using VAdvantageSvc.Classes;
using System.ServiceModel.Activation;
using System.Data;
using VAdvantage.DataBase;
using VAdvantage.Model;
using System.Xml.Linq;
using System.IO;

using System.Dynamic;
using VAdvantage.ProcessEngine;
using VAdvantage.Process;
using System.Web.Configuration;
using System.Diagnostics;

namespace VAdvantageSvc
{
    [ServiceContract(Namespace = "", CallbackContract = typeof(IImportCallback))]
    public interface IExcelImportService
    {
        //[OperationContract]
        //void StartImportProcess(NewImportClientInfo imp, IDictionary<string, string> ctxDic, List<Dictionary<String, Object>> DefaultValues, LocationMappingDetail LocationMapping);


        //[OperationContract]
        //ImportClientInfo LoadImportInfo(int WindowID, IDictionary<string, string> ctxDic);


        //[OperationContract]
        //ImportInfo UploadExcelFileForImport(byte[] fileArray, string fileName, int WindowID, IDictionary<string, string> ctxDic);


        [OperationContract]
        ExcelMapping UploadExcel(byte[] fileArray, String NewFileName, ExcelMapping mapping);

        [OperationContract]
        int SaveMapping(ExcelMapping mapping, ExcelMapping DefaultValues);

        [OperationContract]
        ExcelMapping LoadMappedInfo(ExcelMapping excelMapping);

        [OperationContract]
        bool ImportNow(ExcelMapping excelmapping, List<Dictionary<String, Object>> DefaultValues, OtherImportInfo other,ExcelMapping saveDefaulvales);

        [OperationContract]
        OtherImportInfo LoadStartupInfo(int WindowID, IDictionary<string, string> ctxDic);

        [OperationContract]
        ExcelMapping LoadDefaultValues(ExcelMapping excelmapping);

        
    }

    [AspNetCompatibilityRequirements(RequirementsMode = AspNetCompatibilityRequirementsMode.Allowed)]
    public class ExcelImportService : IExcelImportService
    {
        IImportCallback callback = null;

        //public ImportInfo UploadExcelFileForImport(byte[] fileArray, string fileName, int WindowID, IDictionary<string, string> ctxDic)
        //{
        //    Ctx ctx = new Ctx(ctxDic);
        //    ImportInfo import = new ImportInfo();
        //    try
        //    {
        //        ExcelWrapper wrapper = new ExcelWrapper();
        //        if (fileArray != null) 
        //        {
        //            import.ExcelCols = wrapper.ConnectExcel(fileArray, fileName);
        //            import._ExcelFileName = fileName;
        //            import.info = LoadImportInfo(WindowID, ctxDic);
        //        }
        //        else
        //        {

        //            List<Dictionary<int, string>> list = new List<Dictionary<int, string>>();
        //            List<List<bool>> identifierList = null;
        //            List<int> _tabList = null;

        //            import.WindowList = wrapper.LoadWindowDetail(WindowID, out list, out identifierList, out _tabList, ctx, out import.WindowColumnNames, out import.TablesOnly);

        //            String _sql = "Select Name, AD_Column_ID FROM AD_Column Where IsActive = 'Y' AND AD_Table_ID IN (Select AD_Table_ID From AD_Table where TableName = 'C_Location')";
        //            IDataReader dr = DB.ExecuteReader(_sql);

        //            import.C_Location = new Dictionary<int, string>();
        //            while (dr.Read())
        //            {
        //                import.C_Location.Add(int.Parse(dr[1].ToString()), dr[0].ToString());
        //            }
        //            dr.Close();

        //            import.TabList = _tabList;
        //            import.IdentifierList = identifierList;
        //            import.WindowColumns = list;
        //        }

        //        return import;
        //    }
        //    catch (Exception ex)
        //    {
        //        import.ServiceError = ex.Message + " <==> " + ex.StackTrace;
        //        return import;
        //    }
        //}


        //public void StartImportProcess(NewImportClientInfo imp, IDictionary<string, string> ctxDic, List<Dictionary<String, Object>> DefaultValues, LocationMappingDetail LocationMapping)
        //{
        //    try
        //    {
        //        Ctx ctx = new Ctx(ctxDic);
        //        ImportClientInfoModel model = new ImportClientInfoModel();
        //        model.m_ExcelColIndex = imp.m_ExcelColIndex;
        //        model.m_ExcelColName = imp.m_ExcelColName;
        //        model.m_identifierColumn = imp.m_identifierColumn;
        //        model.m_MappedColumns = imp.m_MappedColumns;
        //        model.m_MappedTables = imp.m_MappedTables;
        //        model.m_AllTables = imp.m_AllTables;
        //        model.MWindowID = imp.MWindowID;
        //        model.TabList = imp.TabList;
        //        model.FileName = imp.FileName;
        //        model.ctx = ctx;
        //        model.OnlySave = imp.OnlySave;

        //        ExcelWrapper wrapper = new ExcelWrapper();
        //        byte[] excelfile;
        //        bool success = wrapper.StartImport(model, out excelfile, DefaultValues, LocationMapping);
        //    }
        //    catch
        //    {
        //    }

        //    //ImportResult result = new ImportResult();
        //    //result.ReturnExcel = null;
        //    //result.IsImported = success;

        //    //return result;
        //}


        //public ImportClientInfo LoadImportInfo(int WindowID, IDictionary<string, string> ctxDic)
        //{
        //    Ctx ctx = new Ctx(ctxDic);
        //    string sql = "SELECT * FROM AD_IMPORTMAP WHERE AD_WINDOW_ID = " + WindowID + " AND AD_Client_ID = " + ctx.GetAD_Client_ID() + " ORDER BY AD_IMPORTMAP_ID";
        //    IDataReader dr = DB.ExecuteReader(sql);
        //    ImportClientInfo info = new ImportClientInfo();
        //    info.m_MappedTables = new List<int>();
        //    info.m_MappedColumns = new List<int>();
        //    info.m_ExcelColIndex = new List<int>();
        //    info.m_ExcelColName = new List<string>();
        //    info.m_identifierColumn = new List<int>();
        //    while (dr.Read())
        //    {
        //        info.m_MappedTables.Add(int.Parse(dr["AD_TABLE_ID"].ToString()));
        //        info.m_MappedColumns.Add(Convert.ToInt32(dr["AD_COLUMN_ID"].ToString()));
        //        info.m_ExcelColName.Add(dr["EXCELCOLNAME"].ToString());
        //        info.m_ExcelColIndex.Add(int.Parse(dr["EXCELCOLINDEX"].ToString()));
        //    }
        //    dr.Close();


        //    sql = "SELECT AD_Column_ID FROM AD_ImportIdentifier WHERE IsActive='Y' and AD_WINDOW_ID = " + WindowID + " AND AD_Client_ID = " + ctx.GetAD_Client_ID() + " ORDER BY AD_ImportIdentifier_ID";
        //    dr = DB.ExecuteReader(sql);
        //    while (dr.Read())
        //    {
        //        info.m_identifierColumn.Add(int.Parse(dr[0].ToString()));
        //    }
        //    dr.Close();

        //    return info;
        //}

        #region NewCode

        public OtherImportInfo LoadStartupInfo(int WindowID, IDictionary<string, string> ctxDic)
        {
            Ctx ctx = new Ctx(ctxDic);
            OtherImportInfo other = new OtherImportInfo();

            try
            {
                //String _sql = "Select Name, AD_Column_ID FROM AD_Column Where IsActive = 'Y' AND AD_Table_ID IN (Select AD_Table_ID From AD_Table where TableName = 'C_Location')";

                String _sql = @"Select
                                Name||(case ismandatory
                                when 'Y'
                                then ' *' 
                                else '' end) as Name , AD_Column_ID FROM AD_Column  Where IsActive = 'Y' AND AD_Table_ID IN (Select AD_Table_ID From AD_Table where TableName = 'C_Location')";
                IDataReader dr = DB.ExecuteReader(_sql);

                while (dr.Read())
                {
                    other.C_Location.Add(int.Parse(dr[1].ToString()), dr[0].ToString());
                }
                dr.Close();

                StringBuilder sb = new StringBuilder("SELECT DISTINCT TBL.AD_TABLE_ID, TABS.NAME || ' ( ' || TBL.TABLENAME || ' ) ' AS TABLENAME, TABS.AD_TAB_ID, TABS.SEQNO, TBL.TABLENAME as ONLYTABLENAME, TABS.ISINFOTAB, TABS.ISADVANCEDTAB FROM ")
                                        .Append("AD_TABLE TBL ")
                                        .Append("INNER JOIN AD_TAB TABS ON TBL.AD_TABLE_ID = TABS.AD_TABLE_ID ")
                                        .Append("INNER JOIN AD_WINDOW WIN ON TABS.AD_WINDOW_ID = WIN.AD_WINDOW_ID ")
                                        .Append("WHERE ");

                sb.Append(" TABS.ISACTIVE='Y' AND TABS.AD_WINDOW_ID=@WindowID ORDER BY TABS.SEQNO ");
                string showAcct = ctx.GetContext("#ShowAcct");
                System.Data.SqlClient.SqlParameter[] param = new System.Data.SqlClient.SqlParameter[1];
                param[0] = new System.Data.SqlClient.SqlParameter("@WindowID", WindowID);

                dr = DB.ExecuteReader(sb.ToString(), param);
                while (dr.Read())
                {
                    if (showAcct.Equals("N") && dr["ISINFOTAB"].ToString().Equals("Y"))
                    {
                        continue;
                    }

                    ExcelMappingPerTabDetail tableinfo = new ExcelMappingPerTabDetail();
                    tableinfo.AD_Table_ID = int.Parse(dr[0].ToString());
                    tableinfo.TableName = dr["TABLENAME"].ToString();
                    tableinfo.AD_Tab_ID = int.Parse(dr["AD_TAB_ID"].ToString());

                    other.AllTables.Add(tableinfo);

                    string sql = @" SELECT cc.AD_Column_ID,
                              cc.ColumnName
                              || ' ('
                              ||
                              (SELECT ad.Name
                                 FROM ad_reference ad
                                WHERE ad.ad_reference_id = cc.ad_reference_id
                              )
                              || ')' AS ColumnWithRef,
                              cc.ColumnName          ,
                              cc.AD_Reference_ID     ,
                                (
                              CASE 
                                when f.name IS NULL
                                THEN to_char(cc.columnname)
                                ELSE to_char (f.Name || ' (' || cc.columnname ||')')
                              END)
                              || (
                              CASE cc.Ismandatory
                                WHEN 'Y'
                                THEN ' *'
                                ELSE ''
                              END) AS ColumnWithField,  cc.ColumnName
                               FROM AD_Column cc
                            LEFT OUTER JOIN ad_field f
                                 ON cc.Ad_Column_Id = f.AD_Column_ID
                            AND f.Ad_Tab_ID         = " + dr[2].ToString() + @"
                              WHERE CC.IsActive = 'Y' AND cc.AD_Table_ID  = " + dr[0].ToString() + @"                             
                              and cc.columnname!='Updated'
                              and cc.columnname!='UpdatedBy'
                              and cc.columnname!='Created'
                              and cc.columnname!='CreatedBy'
                              and cc.columnname!='AD_Client_ID'
                              and cc.columnname!='AD_Org_ID'
                              ORDER BY cc.Ismandatory desc,cc.name ";

                    System.Data.IDataReader drinn = DB.ExecuteReader(sql);
                    while (drinn.Read())
                    {
                        ExcelMappingDetail columninfo = new ExcelMappingDetail();
                        columninfo.AD_Column_ID = int.Parse(drinn["AD_Column_ID"].ToString());
                        columninfo.AD_Column_Name = drinn["ColumnName"].ToString();
                        columninfo.AD_ColumnField_Name = drinn["ColumnWithField"].ToString();
                        columninfo.IsIdentifier = MColumn.Get(ctx, int.Parse(drinn[0].ToString())).IsIdentifier().Equals(true);

                        tableinfo.ColumnDetail.Add(columninfo);
                    }
                    drinn.Close();
                }

            }
            catch (Exception ex)
            {
                other.AnyError = ex.Message;
            }

            return other;
        }

        public int SaveMapping(ExcelMapping mapping, ExcelMapping DefaultValues)
        {
            Ctx ctx = new Ctx(mapping.CtxDic);

            String _sql = "Select AD_ExcelImport_ID from AD_ExcelImport WHERE Upper(Name) = Upper('" + mapping.MappingName + "') AND AD_Client_ID = " + ctx.GetAD_Client_ID();
            Object ob = DB.ExecuteScalar(_sql);
            if (ob != null)
                mapping.ExcelMapping_ID = Convert.ToInt32(ob);

            X_AD_ExcelImport xmapping = new X_AD_ExcelImport(ctx, mapping.ExcelMapping_ID, null);
            xmapping.SetAD_Window_ID(mapping.AD_Window_ID);
            xmapping.SetName(mapping.MappingName);
            xmapping.SetFileName("not-in-use");

            bool result = xmapping.Save();

            int ID = 0;
            if (result)
            {
                ID = xmapping.Get_ID();

                try
                {
                    List<ExcelMappingPerTabDetail> TabDetail = mapping.TabDetail;

                    for (int a = 0; a < TabDetail.Count; a++)
                    {
                        List<ExcelMappingDetail> ColDetail = TabDetail[a].ColumnDetail;
                        if (ColDetail != null)
                        {
                            for (int b = 0; b < ColDetail.Count; b++)
                            {
                                X_AD_ExcelImportMapping xdetail = new X_AD_ExcelImportMapping(ctx, ColDetail[b].PrimaryKey, null);
                                xdetail.SetAD_Column_ID(ColDetail[b].AD_Column_ID);
                                xdetail.SetAD_ExcelImport_ID(ID);
                                xdetail.SetAD_Table_ID(TabDetail[a].AD_Table_ID);
                                xdetail.SetExcelColIndex(ColDetail[b].ExcelColIndex);
                                xdetail.SetExcelColName(ColDetail[b].ExcelColName);
                                xdetail.SetIsIdentifier(ColDetail[b].IsIdentifier);
                                xdetail.SetAD_Tab_ID(TabDetail[a].AD_Tab_ID);

                                if (ColDetail[b].ToDelete)
                                    xdetail.Delete(true);
                                else
                                    xdetail.Save();
                            }

                            List<ExcelMappingDetail> LocationDetail = TabDetail[a].LocationMapping;

                            if (LocationDetail != null)
                            {
                                for (int b = 0; b < LocationDetail.Count; b++)
                                {
                                    X_AD_ExcelImportMapping xdetail = new X_AD_ExcelImportMapping(ctx, LocationDetail[b].PrimaryKey, null);
                                    xdetail.SetAD_Column_ID(LocationDetail[b].AD_Column_ID);
                                    xdetail.SetAD_ExcelImport_ID(ID);
                                    xdetail.SetAD_Table_ID(TabDetail[a].AD_Table_ID);
                                    xdetail.SetExcelColIndex(LocationDetail[b].ExcelColIndex);
                                    xdetail.SetExcelColName(LocationDetail[b].ExcelColName);
                                    xdetail.SetIsIdentifier(false);
                                    xdetail.SetAD_Tab_ID(TabDetail[a].AD_Tab_ID);
                                    xdetail.SetIsLocationMapped(true);

                                    if (LocationDetail[b].ToDelete)
                                        xdetail.Delete(true);
                                    else
                                        xdetail.Save();
                                }
                            }
                        }
                    }




                    //save DefaulValues
                    TabDetail = DefaultValues.TabDetail;
                    for (int a = 0; a < TabDetail.Count; a++)
                    {
                        List<ExcelMappingDetail> ColDetail = TabDetail[a].ColumnDetail;
                        if (ColDetail != null)
                        {
                            for (int b = 0; b < ColDetail.Count; b++)
                            {
                                X_AD_ExcelImportMapping xdetail = new X_AD_ExcelImportMapping(ctx, ColDetail[b].PrimaryKey, null);
                                xdetail.SetAD_Column_ID(ColDetail[b].AD_Column_ID);
                                xdetail.SetAD_ExcelImport_ID(ID);
                                xdetail.SetAD_Table_ID(TabDetail[a].AD_Table_ID);
                                //xdetail.SetExcelColIndex(ColDetail[b].ExcelColIndex);
                                //xdetail.SetExcelColName(ColDetail[b].ExcelColName);
                                //xdetail.SetIsIdentifier(ColDetail[b].IsIdentifier);
                                xdetail.SetAD_Tab_ID(TabDetail[a].AD_Tab_ID);
                                if (ColDetail[b].ExcelColName != null)
                                {
                                    xdetail.SetDefaultData(ColDetail[b].ExcelColName);
                                    if (ColDetail[b].ToDelete)
                                    {
                                        xdetail.Delete(true);
                                    }
                                    else
                                    {
                                        xdetail.Save();
                                    }
                                }
                            }



                        }
                    }

                    //for (int i = 0; i < DefaultValues.Count; i++)
                    //{

                    //    List<string> colList = DefaultValues[i].Keys.ToList();
                    //    for (int j = 0; j < colList.Count; j++)
                    //    {
                    //        object colVlaue = DefaultValues[i][colList[j]];
                    //        if (colVlaue != null)
                    //        {

                    //             X_AD_ExcelImportMapping xdetail = new X_AD_ExcelImportMapping(ctx, 0, null);
                    //             xdetail.SetAD_Column_ID(Convert.ToInt32(DB.ExecuteScalar("Select AD_Column_ID from AD_Column Where columnname='" + colList[j] + "' AND ad_table_id=" + mapping.TabDetail[i].AD_Table_ID)));
                    //             xdetail.SetAD_ExcelImport_ID(ID);
                    //             xdetail.SetAD_Table_ID(mapping.TabDetail[i].AD_Table_ID);
                    //            // xdetail.SetExcelColIndex(LocationDetail[b].ExcelColIndex);
                    //            // xdetail.SetExcelColName(LocationDetail[b].ExcelColName);
                    //            // xdetail.SetIsIdentifier(false);
                    //             xdetail.SetAD_Tab_ID(mapping.TabDetail[i].AD_Tab_ID);
                    //             xdetail.SetIsLocationMapped(true);
                    //             xdetail.SetDefaultData(colVlaue.ToString());                   
                    //             xdetail.Save();
                    //        }
                    //    }
                    //}

                }
                catch
                {
                    ID = 0;
                }
            }

            return ID;
        }

        public ExcelMapping UploadExcel(byte[] fileArray, string fileName, ExcelMapping excelmapping)
        {
            try
            {
                ExcelWrapper wrapper = new ExcelWrapper();
                String newfilename = "";
                List<String> excelcols = wrapper.ConnectExcel(fileArray, fileName);

                excelmapping.FileName = newfilename;
                excelmapping.ExcelCol = excelcols;

                excelmapping = LoadMappedInfo(excelmapping);
                return excelmapping;
            }
            catch
            {
                return null;
            }
        }

        public ExcelMapping LoadMappedInfo(ExcelMapping excelmapping)
        {
            Ctx ctx = new Ctx(excelmapping.CtxDic);
            String _Sql = @"SELECT 
                                AD_ExcelImportMapping_ID, 
                                AD_Column_ID, 
                                ExcelColIndex, 
                                ExcelColName, 
                                AD_Table_ID, 
                                AD_Tab_ID, 
                                IsIdentifier, 
                                IsLocationMapped 
                            FROM 
                                AD_ExcelImportMapping 
                            WHERE 
                                IsActive = 'Y' AND DefaultData is null AND  excelcolname is not null AND AD_ExcelImport_ID = " + excelmapping.ExcelMapping_ID + " Order by AD_ExcelImportMapping_ID";

            IDataReader dr = DB.ExecuteReader(_Sql);

            while (dr.Read())
            {
                int tabIndex = excelmapping.TabDetail.FindIndex((a) => a.AD_Tab_ID == int.Parse(dr["AD_Tab_ID"].ToString()));

                if (tabIndex < 0)
                {

                }

                if (!excelmapping.ExcelCol.Contains(dr["ExcelColName"].ToString()))
                {

                    // excelmapping.ExcelCol.Clear();
                    // return excelmapping;
                    ExcelMappingDetail columnDetail = new ExcelMappingDetail()
                    {
                        AD_Column_ID = int.Parse(dr["AD_Column_ID"].ToString()),
                        AD_Column_Name = MColumn.GetColumnName(ctx, int.Parse(dr["AD_Column_ID"].ToString())),
                       // ExcelColIndex = int.Parse(dr["ExcelColIndex"].ToString()),
                        ExcelColName = dr["ExcelColName"].ToString(),
                        IsIdentifier = dr["IsIdentifier"].Equals("Y"),
                        PrimaryKey = int.Parse(dr["AD_ExcelImportMapping_ID"].ToString()),
                        ToDelete = true
                        //UniqueKey = findUniqueKey.FindIndex((a) => a.Equals(int.Parse(dr["AD_Column_ID"].ToString())))
                    };


                    if (dr["IsLocationMapped"].Equals("Y"))
                    {
                        if (excelmapping.TabDetail[tabIndex].LocationMapping == null)
                            excelmapping.TabDetail[tabIndex].LocationMapping = new List<ExcelMappingDetail>();
                        for (int i = 0; i < excelmapping.TabDetail[tabIndex].LocationMapping.Count; i++)
                        {
                            if (columnDetail.AD_Column_ID == excelmapping.TabDetail[tabIndex].LocationMapping[i].AD_Column_ID)
                            {
                                excelmapping.TabDetail[tabIndex].LocationMapping.RemoveAt(i);
                            }
                        }
                        excelmapping.TabDetail[tabIndex].LocationMapping.Add(columnDetail);
                    }
                    else
                    {
                        if (excelmapping.TabDetail[tabIndex].ColumnDetail == null)
                            excelmapping.TabDetail[tabIndex].ColumnDetail = new List<ExcelMappingDetail>();
                        for (int i = 0; i < excelmapping.TabDetail[tabIndex].ColumnDetail.Count; i++)
                        {
                            if (columnDetail.AD_Column_ID == excelmapping.TabDetail[tabIndex].ColumnDetail[i].AD_Column_ID)
                            {
                                excelmapping.TabDetail[tabIndex].ColumnDetail.RemoveAt(i);
                            }
                        }
                        excelmapping.TabDetail[tabIndex].ColumnDetail.Add(columnDetail);

                    }
                }
                else
                {
                    //List<int> findUniqueKey = new List<int>(excelmapping.TabDetail[tabIndex].AllColumns.Keys);
                    ExcelMappingDetail columnDetail = new ExcelMappingDetail()
                    {
                        AD_Column_ID = int.Parse(dr["AD_Column_ID"].ToString()),
                        AD_Column_Name = MColumn.GetColumnName(ctx, int.Parse(dr["AD_Column_ID"].ToString())),
                     //   ExcelColIndex = int.Parse(dr["ExcelColIndex"].ToString()),
                        ExcelColName = dr["ExcelColName"].ToString(),
                        IsIdentifier = dr["IsIdentifier"].Equals("Y"),
                        PrimaryKey = int.Parse(dr["AD_ExcelImportMapping_ID"].ToString())
                        //UniqueKey = findUniqueKey.FindIndex((a) => a.Equals(int.Parse(dr["AD_Column_ID"].ToString())))
                    };


                    if (dr["IsLocationMapped"].Equals("Y"))
                    {
                        if (excelmapping.TabDetail[tabIndex].LocationMapping == null)
                            excelmapping.TabDetail[tabIndex].LocationMapping = new List<ExcelMappingDetail>();
                        for (int i = 0; i < excelmapping.TabDetail[tabIndex].LocationMapping.Count; i++)
                        {
                            if (columnDetail.AD_Column_ID == excelmapping.TabDetail[tabIndex].LocationMapping[i].AD_Column_ID)
                            {
                             //   excelmapping.TabDetail[tabIndex].ColumnDetail.RemoveAt(i);
                                excelmapping.TabDetail[tabIndex].LocationMapping.RemoveAt(i);
                            }
                        }
                        excelmapping.TabDetail[tabIndex].LocationMapping.Add(columnDetail);
                    }
                    else
                    {
                        if (excelmapping.TabDetail[tabIndex].ColumnDetail == null)
                            excelmapping.TabDetail[tabIndex].ColumnDetail = new List<ExcelMappingDetail>();
                        for (int i = 0; i < excelmapping.TabDetail[tabIndex].ColumnDetail.Count; i++)
                        {
                            if (columnDetail.AD_Column_ID == excelmapping.TabDetail[tabIndex].ColumnDetail[i].AD_Column_ID)
                            {
                                excelmapping.TabDetail[tabIndex].ColumnDetail.RemoveAt(i);
                            }
                        }
                        excelmapping.TabDetail[tabIndex].ColumnDetail.Add(columnDetail);

                    }
                }
            }
            dr.Close();


            return excelmapping;
        }


        public ExcelMapping LoadDefaultValues(ExcelMapping excelmapping)
        {
            try
            {
                Ctx ctx = new Ctx(excelmapping.CtxDic);
                String _Sql = @"SELECT 
                                AD_ExcelImportMapping_ID, 
                                AD_Column_ID, 
                                AD_Table_ID, 
                                AD_Tab_ID, 
                                DefaultData
                            FROM 
                                AD_ExcelImportMapping 
                            WHERE 
                                IsActive = 'Y' AND DefaultData is not null AND AD_ExcelImport_ID = " + excelmapping.ExcelMapping_ID + " Order by AD_ExcelImportMapping_ID";

                IDataReader dr = DB.ExecuteReader(_Sql);

                while (dr.Read())
                {
                    int tabIndex = excelmapping.TabDetail.FindIndex((a) => a.AD_Tab_ID == int.Parse(dr["AD_Tab_ID"].ToString()));

                    if (tabIndex < 0)
                    {

                    }

                    //if (!excelmapping.ExcelCol.Contains(dr["ExcelColName"].ToString()))
                    //{
                    //    excelmapping.ExcelCol.Clear();
                    //    return excelmapping;
                    //}

                    //List<int> findUniqueKey = new List<int>(excelmapping.TabDetail[tabIndex].AllColumns.Keys);
                    ExcelMappingDetail columnDetail = new ExcelMappingDetail()
                    {
                        AD_Column_ID = int.Parse(dr["AD_Column_ID"].ToString()),
                        AD_Column_Name = MColumn.GetColumnName(ctx, int.Parse(dr["AD_Column_ID"].ToString())),
                        //ExcelColIndex = int.Parse(dr["ExcelColIndex"].ToString()),
                        ExcelColName = dr["DefaultData"].ToString(),
                        // IsIdentifier = dr["IsIdentifier"].Equals("Y"),
                        PrimaryKey = int.Parse(dr["AD_ExcelImportMapping_ID"].ToString())
                        //UniqueKey = findUniqueKey.FindIndex((a) => a.Equals(int.Parse(dr["AD_Column_ID"].ToString())))
                    };


                    //if (dr["IsLocationMapped"].Equals("Y"))
                    //{
                    //    if (excelmapping.TabDetail[tabIndex].LocationMapping == null)
                    //        excelmapping.TabDetail[tabIndex].LocationMapping = new List<ExcelMappingDetail>();

                    //    excelmapping.TabDetail[tabIndex].LocationMapping.Add(columnDetail);
                    //}
                    //else
                    //{
                    if (excelmapping.TabDetail[tabIndex].ColumnDetail == null)
                        excelmapping.TabDetail[tabIndex].ColumnDetail = new List<ExcelMappingDetail>();

                    for (int i = 0; i < excelmapping.TabDetail[tabIndex].ColumnDetail.Count; i++)
                    {
                        if (columnDetail.AD_Column_ID == excelmapping.TabDetail[tabIndex].ColumnDetail[i].AD_Column_ID)
                        {
                            excelmapping.TabDetail[tabIndex].ColumnDetail.RemoveAt(i);
                        }
                    }
                    excelmapping.TabDetail[tabIndex].ColumnDetail.Add(columnDetail);

                    // }
                }
                dr.Close();


                return excelmapping;
            }
            catch
            {
                return excelmapping;
            }
        }

        public bool ImportNow(ExcelMapping excelmapping, List<Dictionary<String, Object>> DefaultValues, OtherImportInfo other, ExcelMapping SaveDefaultValues)
        {
            SaveMapping(excelmapping, SaveDefaultValues);
            excelmapping = LoadMappedInfo(excelmapping);
            ExcelWrapper wrapper = new ExcelWrapper();
            wrapper.DoWork(excelmapping, DefaultValues, other);
            return true;
        }

        #endregion

       
    }

    public class LocationMappingDetail
    {
        public List<List<int>> m_LocationExcelIndex { get; set; }
        public List<List<String>> m_LocationDBColumnName { get; set; }
        public bool doCommit { get; set; }
    }

    public interface IImportCallback
    {
        [OperationContract(IsOneWay = true)]
        void QueryExecuted(CallbackDetail cbd);
    }

    public class CallbackDetail
    {
        public String Status { get; set; }
        public String Query { get; set; }
        public String Error { get; set; }
    }

    public class OtherImportInfo
    {
        Dictionary<int, String> _C_Location = new Dictionary<int, string>();

        public Dictionary<int, String> C_Location
        {
            get { return _C_Location; }
            set { _C_Location = value; }
        }

        List<ExcelMappingPerTabDetail> _allTables = new List<ExcelMappingPerTabDetail>();

        public List<ExcelMappingPerTabDetail> AllTables
        {
            get { return _allTables; }
            set { _allTables = value; }
        }

        private String _AnyError;

        public String AnyError
        {
            get { return _AnyError; }
            set { _AnyError = value; }
        }

    }


    #region Excel Mapping New
    /// <summary>
    /// Excel Mapping Info
    /// </summary>
    public class ExcelMapping
    {

        bool _doCommit = false;

        public bool DoCommit
        {
            get { return _doCommit; }
            set { _doCommit = value; }
        }

        String _FileName;

        public String FileName
        {
            get { return _FileName; }
            set { _FileName = value; }
        }

        List<string> _ExcelCol = new List<string>();

        public List<string> ExcelCol
        {
            get { return _ExcelCol; }
            set { _ExcelCol = value; }
        }

        int _ExcelMapping_ID = 0;

        public int ExcelMapping_ID
        {
            get { return _ExcelMapping_ID; }
            set { _ExcelMapping_ID = value; }
        }

        IDictionary<string, string> ctxDic;

        public IDictionary<string, string> CtxDic
        {
            get { return ctxDic; }
            set { ctxDic = value; }
        }

        private String _MappingName = "*New*";

        public String MappingName
        {
            get { return _MappingName; }
            set { _MappingName = value; }
        }

        private int _AD_Window_ID;
        public int AD_Window_ID
        {
            get { return _AD_Window_ID; }
            set { _AD_Window_ID = value; }
        }


        List<ExcelMappingPerTabDetail> _TabDetail = new List<ExcelMappingPerTabDetail>();
        public List<ExcelMappingPerTabDetail> TabDetail
        {
            get { return _TabDetail; }
            set { _TabDetail = value; }
        }
    }


    public class ExcelMappingPerTabDetail
    {
        List<ExcelMappingDetail> _ColumnDetail = new List<ExcelMappingDetail>();

        public List<ExcelMappingDetail> ColumnDetail
        {
            get { return _ColumnDetail; }
            set { _ColumnDetail = value; }
        }

        Dictionary<int, String> _AllColumns = new Dictionary<int, string>();

        public Dictionary<int, String> AllColumns
        {
            get { return _AllColumns; }
            set { _AllColumns = value; }
        }


        List<ExcelMappingDetail> _LocationMapping = new List<ExcelMappingDetail>();
        public List<ExcelMappingDetail> LocationMapping
        {
            get { return _LocationMapping; }
            set { _LocationMapping = value; }
        }

        bool _IsLocationMapped;

        public bool IsLocationMapped
        {
            get { return _IsLocationMapped; }
            set { _IsLocationMapped = value; }
        }

        private int _AD_Table_ID;
        public int AD_Table_ID
        {
            get { return _AD_Table_ID; }
            set { _AD_Table_ID = value; }
        }


        private String _TableName;

        public String TableName
        {
            get { return _TableName; }
            set { _TableName = value; }
        }

        private int _AD_Tab_ID;
        public int AD_Tab_ID
        {
            get { return _AD_Tab_ID; }
            set { _AD_Tab_ID = value; }
        }
    }

    /// <summary>
    /// Excel Mapping Detail
    /// </summary>
    public class ExcelMappingDetail
    {
        public ExcelMappingDetail()
        {
        }

        private int _PrimaryKey = 0;

        public int PrimaryKey
        {
            get { return _PrimaryKey; }
            set { _PrimaryKey = value; }
        }

        private int _UniqueKey;

        public int UniqueKey
        {
            get { return _UniqueKey; }
            set { _UniqueKey = value; }
        }


        private int _ExcelColIndex;

        public int ExcelColIndex
        {
            get { return _ExcelColIndex; }
            set { _ExcelColIndex = value; }
        }

        private String _ExcelColName;

        public String ExcelColName
        {
            get { return _ExcelColName; }
            set { _ExcelColName = value; }
        }

        private int _AD_Column_ID;

        public int AD_Column_ID
        {
            get { return _AD_Column_ID; }
            set { _AD_Column_ID = value; }
        }

        private String _AD_Column_Name;

        public String AD_Column_Name
        {
            get { return _AD_Column_Name; }
            set { _AD_Column_Name = value; }
        }

        private String _AD_ColumnField_Name;

        public String AD_ColumnField_Name
        {
            get { return _AD_ColumnField_Name; }
            set { _AD_ColumnField_Name = value; }
        }

        private bool _IsIdentifier;

        public bool IsIdentifier
        {
            get { return _IsIdentifier; }
            set { _IsIdentifier = value; }
        }

        bool _ToDelete = false;

        public bool ToDelete
        {
            get { return _ToDelete; }
            set { _ToDelete = value; }
        }

        //private String defaultValue;
        //public String DefaultValue
        //{
        //    get { return defaultValue; }
        //    set { defaultValue=value;}
        //}
    }

    #endregion

}
